﻿using FAMailUtils.MailGun;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FAGateway.Common
{
    class DataUtils
    {

        static log4net.ILog logs_error = log4net.LogManager.GetLogger("ErrorRollingLogFileAppender");
        static log4net.ILog logs_info = log4net.LogManager.GetLogger("InfoRollingLogFileAppender");
        static log4net.ILog logs_scan = log4net.LogManager.GetLogger("ScanEmailAppender");

        static DataTable GetDataTableByComand(SqlCommand cmd)
        {
            DataTable result = new DataTable();
            using (SqlConnection connect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                cmd.Connection = connect;
                SqlDataAdapter data = new SqlDataAdapter(cmd);
                data.Fill(result);
            }
            return result;
        }
        static bool ExecuteNonQueryComand(SqlCommand cmd)
        {
            int i = 0;
            using (SqlConnection connect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                connect.Open();
                cmd.Connection = connect;
                int.TryParse(cmd.ExecuteNonQuery() + "", out i);
            }
            return i > 0;
        }
        /// <summary>
        /// tracking delivery
        /// </summary>
        /// <returns></returns>
        public static DataTable GetDomainSenderTrackingDelivery()
        {
            int CheckResultBackCount = 0;
            int.TryParse(ConfigurationManager.AppSettings["CheckResultBackCount"] + "", out CheckResultBackCount);
            if (CheckResultBackCount == 0) CheckResultBackCount = 1;
            string sql = @"select distinct domain from (
                            select [from] 
                            , substring([from],CHARINDEX('@', [from])+1, LEN([from])-CHARINDEX('@', [from])) domain
                            from tblSendMail where sendstatus in (-1) and sendtime >= dateadd(day, -" + CheckResultBackCount + @", getdate())
                            )a";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            return GetDataTableByComand(cmd);
        }
        /// <summary>
        /// tracking open & click
        /// </summary>
        /// <returns></returns>
        public static DataTable GetDomainSenderTrackingOpenClick()
        {
            int CheckResultBackCount = 0;
            int.TryParse(ConfigurationManager.AppSettings["CheckResultBackCount"] + "", out CheckResultBackCount);
            if (CheckResultBackCount == 0) CheckResultBackCount = 1;
            string sql = @"select distinct domain from (
                            select [from] 
                            , substring([from],CHARINDEX('@', [from])+1, LEN([from])-CHARINDEX('@', [from])) domain
                            from tblSendMail where sendstatus in (0) and sendtime >= dateadd(day, -" + CheckResultBackCount + @", getdate())
                            )a";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            return GetDataTableByComand(cmd);
        }

        internal static bool UpdateSendItemResult(FAMailUtils.MailGun.LogItem item)
        {
            SqlCommand cmd = new SqlCommand();

            string sql = "";
            //rejected OR failed OR delivered OR opened OR clicked
            if (item.Event == "rejected" || item.Event == "failed")
            {
                sql = @"update tblSendMail set sendstatus=" + (int)sendStatus.Bounce + " where sendstatus = -1 and messageid like'%" + item.messageId + "%'";
                cmd.CommandText = sql;
                ExecuteNonQueryComand(cmd);

                cmd = new SqlCommand("SP_LogBounceEmail");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@FromEmail", SqlDbType.NVarChar, 250)).Value = item.from;
                cmd.Parameters.Add(new SqlParameter("@ToEmail", SqlDbType.NVarChar, 250)).Value = item.to;
                cmd.Parameters.Add(new SqlParameter("@SendTime", SqlDbType.NVarChar, 250)).Value = item.reason;
                cmd.Parameters.Add(new SqlParameter("@BounceType", SqlDbType.NVarChar, 250)).Value = item.Event;
                ExecuteNonQueryComand(cmd);
            }

            else if (item.Event == "delivered")
                sql = @"update tblSendMail set sendstatus=" + (int)sendStatus.Delivery + " where sendstatus = -1 and messageid like'%" + item.messageId + "%'";

            else if (item.Event == "opened")
                sql = @"update tblSendMail set sendstatus=" + (int)sendStatus.Delivery + ", isOpen=1, OpenDate=getdate() where sendstatus =" + (int)sendStatus.Delivery + " and messageid like'%" + item.messageId + "%'";
            else if (item.Event == "clicked")
                sql = @"update tblSendMail set sendstatus=" + (int)sendStatus.Delivery + ", isClick=1 where sendstatus =" + (int)sendStatus.Delivery + " and messageid like'%" + item.messageId + "%'";
            cmd = new SqlCommand();
            cmd.CommandText = sql;
            return ExecuteNonQueryComand(cmd);
        }
        internal static bool UpdateSendItemResult(FAMailUtils.MailGun.TrackingLogItem item)
        {
            SqlCommand cmd = new SqlCommand();

            string sql = "";
            if (item.Event == "opened")
                sql = @"update tblSendMail set sendstatus=" + (int)sendStatus.Delivery + ", isOpen=1, OpenDate=getdate() where sendstatus =" + (int)sendStatus.Delivery + " and messageid like'%" + item.messageId + "%'";
            else if (item.Event == "clicked")
                sql = @"update tblSendMail set sendstatus=" + (int)sendStatus.Delivery + ", isClick=1 where sendstatus =" + (int)sendStatus.Delivery + " and messageid like'%" + item.messageId + "%'";
            cmd = new SqlCommand();
            cmd.CommandText = sql;
            return ExecuteNonQueryComand(cmd);
        }
        public static List<SendItem> GetMail2Send()
        {
            using (SqlConnection connect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                List<SendItem> items = new List<SendItem>();
                try
                {
                    SqlCommand cmd = new SqlCommand("SP_GetSendMail_New", connect);
                    SqlDataAdapter data = new SqlDataAdapter(cmd);
                    cmd.CommandType = CommandType.StoredProcedure;
                    DataSet ds = new DataSet();
                    data.Fill(ds);
                    SendItem item;
                    if (ds != null && ds.Tables.Count > 0)
                    {

                        DataTable T = ds.Tables[0];
                        DataTable content = ds.Tables[1];
                        foreach (DataRow r in T.Rows)
                        {
                            item = new SendItem();
                            item.id = Convert.ToDecimal(r["id"]);
                            item.From = r["from"] + "";
                            item.To = r["to"] + "";
                            item.CustomerName = r["name"] + "";


                            if (r["sendRegisterId"] + "" != "")
                            {
                                if (content.Select("sendRegisterId=" + r["sendRegisterId"]).Length > 0)
                                {
                                    item.sendRegisterId = Convert.ToInt32(r["sendRegisterId"]);
                                    item.Subject = content.Select("sendRegisterId=" + r["sendRegisterId"])[0]["subject"] + "";
                                    item.Body = content.Select("sendRegisterId=" + r["sendRegisterId"])[0]["body"] + "";
                                    item.SenderName = content.Select("sendRegisterId=" + r["sendRegisterId"])[0]["ConfigName"] + "";
                                    if (item.CustomerName + "" == "" || item.CustomerName + "" == "Không có")
                                        item.Body = item.Body.Replace("[khachhang]", "Bạn");
                                    else item.Body = item.Body.Replace("[khachhang]", item.CustomerName);
                                    string Unreceve = String.Format("<p style='text-align: center;font-size: 8px;'>" +
                                    "<span style='color: rgb(102, 102, 102); font-family: Arial, Helvetica, sans-serif;'>If you have no more interest in receiving this, please unsubscribe.</span></p>" +
                                    "<p style='text-align: center;font-size: 8px;'>" +
                                    "<strong> Để ngừng nhận mail vui lòng click <a href='" + ConfigurationManager.AppSettings["site"] + "/backend/UnReciveMail.aspx?id={0}'>vào đây</a></strong></p>", item.id);
                                    item.Body += Unreceve;
                                    item.Body += String.Format("<IMG height=1 src=\"" + ConfigurationManager.AppSettings["site"] + "/backend/emailtrack.aspx?id={0}\" width=1>", item.id);

                                    items.Add(item);
                                }
                            }
                            else if (r["contentSendEventId"] + "" != "")
                            {
                                if (content.Select("contentSendEventId=" + r["contentSendEventId"]).Length > 0)
                                {
                                    item.contentSendEventId = Convert.ToInt32(r["contentSendEventId"]);
                                    item.Subject = content.Select("contentSendEventId=" + r["contentSendEventId"])[0]["subject"] + "";
                                    item.Body = content.Select("contentSendEventId=" + r["contentSendEventId"])[0]["body"] + "";
                                    item.SenderName = content.Select("contentSendEventId=" + r["contentSendEventId"])[0]["ConfigName"] + "";

                                    if (item.CustomerName + "" == "" || item.CustomerName + "" == "Không có")
                                        item.Body = item.Body.Replace("[khachhang]", "Bạn");
                                    else item.Body = item.Body.Replace("[khachhang]", item.CustomerName);

                                    string Unreceve = String.Format("<p style='text-align: center; font-size: 8px'>" +
                                    "<span style='color: rgb(102, 102, 102); font-family: Arial, Helvetica, sans-serif;'>If you have no more interest in receiving this, please unsubscribe.</span></p>" +
                                    "<p style='text-align: center;font-size: 8px'>" +
                                    "<strong> Để ngừng nhận mail vui lòng click <a href='" + ConfigurationManager.AppSettings["site"] + "/backend/UnReciveMail.aspx?id={0}'>vào đây</a></strong></p>", item.id);
                                    item.Body += Unreceve;
                                    item.Body += String.Format("<IMG height=1 src=\"" + ConfigurationManager.AppSettings["site"] + "/backend/emailtrack.aspx?id={0}\" width=1>", item.id);

                                    items.Add(item);
                                }
                            }
                            else if (r["CustomerCareID"] + "" != "")
                            {
                                if (content.Select("CustomerCareID=" + r["CustomerCareID"]).Length > 0)
                                {
                                    item.contentSendEventId = Convert.ToInt32(r["CustomerCareID"]);
                                    item.Subject = content.Select("CustomerCareID=" + r["CustomerCareID"])[0]["subject"] + "";
                                    item.Body = content.Select("CustomerCareID=" + r["CustomerCareID"])[0]["body"] + "";
                                    item.SenderName = content.Select("CustomerCareID=" + r["CustomerCareID"])[0]["ConfigName"] + "";

                                    if (item.CustomerName + "" == "" || item.CustomerName + "" == "Không có")
                                        item.Body = item.Body.Replace("[khachhang]", "Bạn");
                                    else item.Body = item.Body.Replace("[khachhang]", item.CustomerName);

                                    string Unreceve = String.Format("<p style='text-align: center; font-size: 8px'>" +
                                    "<span style='color: rgb(102, 102, 102); font-family: Arial, Helvetica, sans-serif;'>If you have no more interest in receiving this, please unsubscribe.</span></p>" +
                                    "<p style='text-align: center;font-size: 8px'>" +
                                    "<strong> Để ngừng nhận mail vui lòng click <a href='" + ConfigurationManager.AppSettings["site"] + "/backend/UnReciveMail.aspx?id={0}'>vào đây</a></strong></p>", item.id);
                                    item.Body += Unreceve;
                                    item.Body += String.Format("<IMG height=1 src=\"" + ConfigurationManager.AppSettings["site"] + "/backend/emailtrack.aspx?id={0}\" width=1>", item.id);

                                    items.Add(item);
                                }
                            }
                            else if (r["CustomerCareAuto_ContentID"] + "" != "")
                            {
                                if (content.Select("CustomerCareAuto_ContentID=" + r["CustomerCareAuto_ContentID"]).Length > 0)
                                {
                                    item.contentSendEventId = Convert.ToInt32(r["CustomerCareAuto_ContentID"]);
                                    item.Subject = content.Select("CustomerCareAuto_ContentID=" + r["CustomerCareAuto_ContentID"])[0]["subject"] + "";
                                    item.Body = content.Select("CustomerCareAuto_ContentID=" + r["CustomerCareAuto_ContentID"])[0]["body"] + "";
                                    item.SenderName = content.Select("CustomerCareAuto_ContentID=" + r["CustomerCareAuto_ContentID"])[0]["ConfigName"] + "";

                                    if (item.CustomerName + "" == "" || item.CustomerName + "" == "Không có")
                                        item.Body = item.Body.Replace("[khachhang]", "Bạn");
                                    else item.Body = item.Body.Replace("[khachhang]", item.CustomerName);

                                    string Unreceve = String.Format("<p style='text-align: center; font-size: 8px'>" +
                                    "<span style='color: rgb(102, 102, 102); font-family: Arial, Helvetica, sans-serif;'>If you have no more interest in receiving this, please unsubscribe.</span></p>" +
                                    "<p style='text-align: center;font-size: 8px'>" +
                                    "<strong> Để ngừng nhận mail vui lòng click <a href='" + ConfigurationManager.AppSettings["site"] + "/backend/UnReciveMail.aspx?id={0}'>vào đây</a></strong></p>", item.id);
                                    item.Body += Unreceve;
                                    item.Body += String.Format("<IMG height=1 src=\"" + ConfigurationManager.AppSettings["site"] + "/backend/emailtrack.aspx?id={0}\" width=1>", item.id);

                                    items.Add(item); ;
                                }
                            }
                        }

                    }
                }
                catch (Exception ex) { logs_error.Error("GetMail2Send", ex); }
                return items;
            }
        }

        internal static void UpdateSendItem(SendItem sendItem)
        {
            string sql = "Update tblSendMail set messageId=@messageId, sendtime=getdate(),senddate=DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())), sendStatus=@sendStatus where id=@id";
            using (SqlConnection connect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(sql, connect);
                    connect.Open();
                    SqlParameter para = new SqlParameter("messageId", SqlDbType.NVarChar, 150);
                    para.Value = sendItem.messageId;
                    cmd.Parameters.Add(para);

                    para = new SqlParameter("@Id", SqlDbType.Decimal);
                    para.Value = sendItem.id;
                    cmd.Parameters.Add(para);

                    para = new SqlParameter("@sendStatus", SqlDbType.Int);
                    para.Value = sendItem.isSend ? 0 : 3;
                    cmd.Parameters.Add(para);
                    cmd.ExecuteNonQuery();
                    connect.Close();
                }
                catch (Exception ex) { logs_error.Error("UpdateSendItem", ex); }
            }
        }
        internal static void UpdateSendItem(SendResult sendItem)
        {
            string sql = "Update tblSendMail set messageId=@messageId, sendtime=getdate(),senddate=DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())), sendStatus=@sendStatus where id=@id";
            using (SqlConnection connect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(sql, connect);
                    connect.Open();
                    SqlParameter para = new SqlParameter("messageId", SqlDbType.NVarChar, 150);
                    para.Value = sendItem.id == null ? sendItem.message : sendItem.id;
                    cmd.Parameters.Add(para);

                    para = new SqlParameter("@Id", SqlDbType.Decimal);
                    para.Value = sendItem.item_id;
                    cmd.Parameters.Add(para);

                    para = new SqlParameter("@sendStatus", SqlDbType.Int);
                    para.Value = sendItem.IsSent ? sendStatus.Sent : sendStatus.NotSend;
                    cmd.Parameters.Add(para);
                    cmd.ExecuteNonQuery();
                    connect.Close();
                }
                catch (Exception ex) { logs_error.Error("UpdateSendItem", ex); }
            }
        }
        internal static void UpdateBounceResultToCustomer()
        {
            using (SqlConnection connect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("SP_UpdateBounceResultToCustomer", connect);
                    connect.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();
                    connect.Close();
                }
                catch (Exception ex) { logs_error.Error("UpdateSendItem", ex); }
            }
        }

        internal void UpdateMailConfig(Domain item)
        {
            using (SqlConnection connect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                try
                {
                    string sql = "Update tblMailConfig set dnsVerified =1 where db_owner.f_getDomain(Email) ='" + item.name + "'";

                    SqlCommand cmd = new SqlCommand(sql, connect);
                    connect.Open();
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    connect.Close();
                }
                catch (Exception ex) { logs_error.Error("UpdateSendItem", ex); }
            }
        }

        internal static DataTable GetDomainToVerifyEmail()
        {
            string sql = @"SP_GetDomainToVerify";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sql;
            return GetDataTableByComand(cmd);
        }

        internal static object UpdateEmailByDomainFail(string domain)
        {
            object result = null;
            using (SqlConnection connect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                try
                {
                    string sql = "Update tblCustomer set recivedEmail=0, isVerified=1,verifytime=getdate() where db_owner.f_getDomain(Email) ='" + domain + "'";

                    SqlCommand cmd = new SqlCommand(sql, connect);
                    connect.Open();
                    cmd.CommandType = CommandType.Text;
                    result = cmd.ExecuteNonQuery();
                    connect.Close();
                }
                catch (Exception ex) { logs_error.Error("UpdateSendItem", ex); }
                return result;
            }
        }
        internal static object UpdateEmailByDomainOK(string domain)
        {
            object result = null;
            using (SqlConnection connect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                try
                {
                    string sql = "Update tblCustomer set isVerified=1 where db_owner.f_getDomain(Email) ='" + domain + "'";

                    SqlCommand cmd = new SqlCommand(sql, connect);
                    connect.Open();
                    cmd.CommandType = CommandType.Text;
                    result = cmd.ExecuteNonQuery();
                    connect.Close();
                }
                catch (Exception ex) { logs_error.Error("UpdateSendItem", ex); }
                return result;
            }
        }

        internal static void CheckDomainLimit()
        {
            using (SqlConnection connect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                try
                {
                    string sql = @"select distinct domain from tblMailConfig where status_0N_1W_2B <> 2 and isDelete <> 1";
                    SqlCommand cmd = new SqlCommand(sql, connect);
                    connect.Open();
                    SqlDataAdapter data = new SqlDataAdapter(cmd);
                    DataTable T = new DataTable();
                    data.Fill(T);
                    foreach (DataRow row in T.Rows)
                    {
                        sql = "[SP_GetDomainSendResult] '" + row[0] + "'";
                        cmd = new SqlCommand(sql, connect);
                        data = new SqlDataAdapter(cmd);
                        DataTable table = new DataTable();
                        data.Fill(table);
                        if (table != null && table.Rows.Count > 0)
                        {
                            if (table.Rows[0][0] + "" != "")
                            {
                                string content = table.Rows[0][0] + "";
                                logs_scan.Info(content.Replace("Email {1}", "Domain " + row[0]));
                                sql = @"select distinct domain, u.Username, c.email
                                    from tblMailConfig c
                                    inner join tblUserLogin u on u.UserId = c.UserId
                                    where c.isDelete <> 1 and domain='" + row[0] + "'";
                                cmd = new SqlCommand(sql, connect);
                                data = new SqlDataAdapter(cmd);
                                DataTable source = new DataTable();
                                data.Fill(source);
                                foreach (DataRow r in source.Rows)
                                {
                                    SendLockMail2Client(r[1] + "", r[2] + "", content);
                                }
                            }
                        }
                    }
                    connect.Close();
                }
                catch (Exception ex) { logs_error.Error("CheckDomainLimit", ex); }
            }
        }
        static void SendLockMail2Client(string to, string mailSend, string content)
        {
            string body = @"Chào {0}.<br/><br/>
                            " + content + @", để bảo vệ uy tín tên miền của bạn với các ISP chúng tôi đã khóa email này.
                            <br/>Chúng tôi rất muốn nghe từ bạn, làm cách nào để không gửi email chết vượt hạn mức qui định không.
                            <br/>Chúng tôi rất muốn nghe sớm từ bạn để mở lại trường hợp của bạn được gửi bình thường.
                            <br/><br/>
                            Trân trọng,<br/>
                            Nhóm phát triển FaMails.com
                            ";
            string subject = "Cảnh báo tài khoản";
            string from = ConfigurationManager.AppSettings["SupportEmail"];
            FAMailUtils.MailGun.SendMail utils = new SendMail(ConfigurationManager.AppSettings["api-key"]);
            utils.SendHtmlMessage(from.Split('@')[1], "Hệ thống FAMails.com", from, to, subject, string.Format(body, to, mailSend), false);
            utils.SendHtmlMessage(from.Split('@')[1], "Hệ thống FAMails.com", from, from, subject, string.Format(body, to, mailSend), false);
        }

        internal static int CheckMailConfig(string domain)
        {
            int result = 0;
            using (SqlConnection connect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                try
                {
                    string sql = "SP_CheckMailConfig '" + domain + "'";
                    SqlCommand cmd = new SqlCommand(sql, connect);
                    connect.Open();
                    object obj = cmd.ExecuteScalar();
                    connect.Close();
                    int.TryParse(obj + "", out result);
                }
                catch (Exception ex) { logs_error.Error("CheckDomainLimit", ex); }
            }
            return result;
        }

        internal static DataTable GetDomainSendStatus(string domain)
        {
            string sql = @"SP_GetDomainSendStatus '" + domain + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            return GetDataTableByComand(cmd);
        }

        internal static int BanDomain(object domain, bool isBan)
        {
            int result = 0;
            using (SqlConnection connect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
            {
                try
                {
                    string sql = "update tblMailConfig set status_0N_1W_2B=2 where domain = '" + domain + "'";
                    if (!isBan)
                        sql = "update tblMailConfig set status_0N_1W_2B=0, LastActive=getdate() where domain = '" + domain + "'";
                    SqlCommand cmd = new SqlCommand(sql, connect);
                    connect.Open();
                    object obj = cmd.ExecuteScalar();
                    connect.Close();
                    int.TryParse(obj + "", out result);
                }
                catch (Exception ex) { logs_error.Error("BanDomain", ex); }
            }
            return result;
        }
    }
}
